package com.fm.jbsql.entry;

import com.fm.jbsql.sql.DbDriver;
import com.fm.jbsql.sql.DbDriverTypeSelector;
import com.fm.jbsql.sql.SQLScriptCompiler;
import com.fm.jbsql.util.CmdOpt;
import com.fm.jbsql.util.JbsqlException;

import java.io.*;
import java.sql.*;
import java.util.Properties;
import java.util.Vector;
import java.util.logging.Logger;

/**
 * Created by IntelliJ IDEA.
 * User: syaroshenko
 * Date: Jul 13, 2006
 * Time: 11:36:27 AM
 * Class for project management.
 */

/**
 * Class for project management.
 */
public abstract class Project {
    private final static Logger logger = Logger.getLogger("jbsql");
    CmdOpt cmdopt;

    /**
     * Set cmdopt class member.
     * @param _cmdopt command line options
     */
    final void setCmdOpt(final CmdOpt _cmdopt) {
        logger.fine("start");
        if ( _cmdopt == null ) throw new NullPointerException("ERROR: null CmdOpt passed as argument.");
        cmdopt = _cmdopt;
        logger.fine("finish");
    }

    /**
     * Recursively traverse directories.
     * @param dir directory
     * @param level maximum nested level
     * @param filesTree files tree to append
     * @param sqlOnly save only sql objects
     * @param dirsOnly save only directories
     * @param skipSVN skip .svn directory
     * @throws NullPointerException
     */
    public static void findNextFile (String dir, int level, Vector filesTree, boolean sqlOnly, boolean dirsOnly, boolean skipSVN) throws NullPointerException {
        logger.fine("start");
        if ( filesTree == null ) throw new NullPointerException("ERROR: null vector passed as argument.");

        if ( 0 == level-- ) return;

        File path = new File(dir);
        String[] list = path.list();
        if ( list == null ) return;

        File f;
        for (int i = 0; i < list.length; i++) {
            if ( skipSVN && ".svn".equals(list[i])) continue;
            String s = dir + File.separator + list[i];
            f = new File(s);
            if ( f.isDirectory() ) {
                if ( dirsOnly ) filesTree.add(s);
                findNextFile(s, level, filesTree, sqlOnly, dirsOnly, skipSVN);
            }
            else {
                if ( sqlOnly ) {
                    if ( s.toLowerCase().endsWith(".pro") || s.toLowerCase().endsWith(".sql") ) {
                        if ( !dirsOnly ) filesTree.add(s);
                    }
                }
                else {
                    if ( !dirsOnly ) filesTree.add(s);
                }
            }
        }
        logger.fine("finish");
    }

    /**
     * Parse entries.
     * @param f File
     * @param v Vector of resulting entries
     * @param bSrcShouldExist flag to throw error unless entries source exists
     * @param isGetRevisionMode mode to retrieve revision of pushed files only
     * @param inputFiles pushed files to retrieve revision
     * @throws NullPointerException
     * @throws JbsqlException
     */
    void parseEntries(File f, Vector v, boolean bSrcShouldExist, boolean isGetRevisionMode, String []inputFiles) throws NullPointerException, JbsqlException {
        logger.fine("start");
        if ( f == null ) throw new NullPointerException("ERROR: null file passed as argument.");
        if ( v == null ) throw new NullPointerException("ERROR: null vector passed as argument.");

        logger.fine("finish");
    }

    /**
     * Verify entry source integrity: check if files physically exist.
     * @throws JbsqlException
     */
    abstract void verifyEntrySourceIntegrity() throws JbsqlException;

    /**
     * Generate sql.lst: recursively traverse directories and push SQL-objects into list.
     * @throws JbsqlException
     */
    private void genSqlLst () throws JbsqlException {
        logger.fine("start");
        // build files tree
        Vector filesTree = new Vector();
        Vector filesSqlLst = new Vector();

        findNextFile (".", 255, filesTree, true, false, true);
        int filesTreeSize;
        if ( (filesTreeSize=filesTree.size()) == 0 ) return;

        // create sql.lst unless it exists
        File f = new File("sql.lst");
        if ( !f.exists() ) {
            try {f.createNewFile();}
            catch (IOException ex) {
                throw new JbsqlException("IOE_01", "sql.lst", ex);
            }
        }
        else {
            // read existing entries
            parseEntries(f, filesSqlLst, true, false, null);
        }

        try {
            BufferedWriter os = new BufferedWriter(new OutputStreamWriter(new FileOutputStream(f, true), "UTF8"));
            int filesSqlLstSize = filesSqlLst.size();
            for ( int i = 0; i < filesTreeSize; i++ ) {
                boolean entryExists = false;
                for ( int j = 0; j < filesSqlLstSize; j++ ) {
                    if ( ((String)filesTree.get(i)).substring(2).replace('/', '\\').equals(((Entry)filesSqlLst.get(j)).name) ) {
                        entryExists = true;
                        break;
                    }
                }
                if ( !entryExists ) {
                    // always save windows-like path separators
                    os.write(filesTree.get(i).toString().substring(2).replace('/', '\\'));
                    os.write('\n');
                }
            }
            os.close();
        } catch (IOException ex) {
            throw new JbsqlException("IOE_02", "sql.lst", ex);
        }
        logger.fine("finish");
    }

    /**
     * Run project.
     * @param bSrcShouldExist flag to throw error unless entries source exists
     * @throws JbsqlException
     */
    public final void run(boolean bSrcShouldExist) throws JbsqlException {
        logger.fine("start");

        // Generate sql.lst
        if ( cmdopt.isGen() ) {
            genSqlLst();
            return;
        }

        // Test mode: verify sql.lst or .svn\entries file list integrity
        if ( cmdopt.isDryrun() && cmdopt.getUserName() == null ) {
            verifyEntrySourceIntegrity();
            return;
        }

        // cleanup output file
        if ( cmdopt.getOutputFileName() != null ) {
            File f = new File(cmdopt.getOutputFileName());
            if ( f.exists() ) f.delete();
        }

        // Parse entries
        Vector entries = new Vector();
        if ( cmdopt.getFileNameAskingRevision() != null ) {
            parseEntries(cmdopt.getEntriesSource(), entries, bSrcShouldExist, true, cmdopt.getFileNameAskingRevision().split(";"));

            PrintStream outputStream;
            try { outputStream = cmdopt.getOutputFileName() == null ? System.out : new PrintStream(new FileOutputStream(cmdopt.getOutputFileName(), true)); }
            catch (FileNotFoundException ex) { throw new JbsqlException("FNF_01", cmdopt.getOutputFileName(), ex); }

            for ( int loop = 0; loop < entries.size(); loop++ ) {
                outputStream.println(cmdopt.getStringBefore()+((Entry)(entries.get(loop))).revision+cmdopt.getStringAfter());
                outputStream.flush();
            }

            if ( cmdopt.getOutputFileName() != null ) outputStream.close();

            return;
        }

        if ( cmdopt.getInputFileName() == null ) {
            parseEntries(cmdopt.getEntriesSource(), entries, bSrcShouldExist, false, null);
            if ( entries.size() == 0 ) {
                logger.info("Project " + (new File(cmdopt.getPath())) + ": nothing to do.");
                return;
            }
        }

        // Create hst_bsql table
        Connection conn = null;
        DbDriver drv = DbDriverTypeSelector.getDriver(cmdopt.getDriverType());
        try {
            Class.forName(drv.getJdbcClass()).newInstance();
        } catch (Exception ex) {
            throw new JbsqlException("EXP_01", "[" + drv.getJdbcClass() + "]", ex);
        }

        logger.info("Connecting to database " + cmdopt.getDatabase() + "[" + cmdopt.getDriverType().getName() + "] at " + cmdopt.getServerName() + " with login " + cmdopt.getUserName() + "..");
        try {
            conn = this.createConnection(drv);
            drv.initConnection(conn);

            if ( cmdopt.getInputFileName() != null ) {
                logger.info("Executing " + cmdopt.getInputFileName() + "..");
                drv.getCompiler().compile(cmdopt, drv, conn, cmdopt.getInputFileName());
                logger.info("Done.");

                return;
            }

            String projectNameSplitted[], projectName;
            try { projectNameSplitted = (new File(cmdopt.getPath())).getCanonicalPath().split("(/)|(\\\\)"); }
            catch (IOException ex) { throw new JbsqlException("IOE_03", null, ex); }
            projectName = projectNameSplitted.length > 0 ? projectNameSplitted[projectNameSplitted.length - 1] : "";

            // create hst_bsql table
            Statement stmt = conn.createStatement();
            String cols1[][] = {{"hbs_prj varchar(50)", "not null", "collate database_default"},
                                {"hbs_file varchar(255)", "not null", "collate database_default"},
                                {"hbs_who varchar(20)","not null", "collate database_default"},
                                {"hbs_date datetime", "not null", ""},
                                {"primary key (hbs_prj, hbs_file)", "", ""}};
            String query = drv.getCreateTableStmt(false, true, "hst_bsql", cols1);
            logger.info("Executing query [" + query + "]..");
            stmt.executeUpdate(query);
            drv.checkForWarning(stmt.getWarnings());
            stmt.clearWarnings();
            logger.info("done.");

            // create temp table for entry candidates
            String cols2[][] = {{"sort_order int", "null", ""},
                                {"name varchar(255)", "", "collate database_default"},
                                {"revision varchar(50)", "", "collate database_default"}};
            query = drv.getCreateTableStmt(true, false, "tmp_bsql", cols2);
            logger.info("Executing query [" + query + "]..");
            stmt.executeUpdate(query);
            drv.checkForWarning(stmt.getWarnings());
            stmt.clearWarnings();
            logger.info("done.");

            // insert candidates
            logger.info("Executing batch query with " + entries.size() + " entries.");
            stmt = conn.createStatement();
            conn.setAutoCommit(false);
            try {
                for ( int loop = 0; loop < entries.size(); loop++ ) {
                    query = "insert into " + drv.tempTableSymbol() + "tmp_bsql values ("+ loop + ", \"" +
                            ((Entry)(entries.get(loop))).name + "\",\"" +
                            ((Entry)(entries.get(loop))).revision + "\")\n";
                    logger.info("query: " + query);
                    stmt.addBatch(query);
                }
                stmt.executeBatch();
            } catch (BatchUpdateException bue) {
                throw new JbsqlException("SQL_01", null, bue);
            }
            conn.commit();
            conn.setAutoCommit(true);

            drv.checkForWarning(stmt);
            logger.info("done.");

            // retrieve objects to compile
            query = "select distinct name, revision, sort_order from " + drv.tempTableSymbol() + "tmp_bsql tbs where lower("+ (cmdopt.isSqllst() ? "" : ("\""+projectName+"\\\"+")) +"name) " + drv.getCollationDefaultStmt() +
                    " not in (select lower(hbs_file) from hst_bsql where " + (cmdopt.isSqllst() ? ("lower(tbs.name) "+ drv.getCollationDefaultStmt()) : ("lower(\""+projectName+"\\\"+tbs.name)")) + "=lower(hbs_file) and hbs_prj=\"" +
                    (cmdopt.isSqllst() ? (projectName + "\")") : ("rev:\"+tbs.revision)")) + " order by sort_order";

            logger.info("Executing query [" + query + "]..");
            ResultSet rs = stmt.executeQuery(query);
            drv.checkForWarning(stmt);
            logger.info("done.");

            Statement stmtRows = conn.createStatement();
            ResultSet rs1;
            boolean bRowExists;
            while ( rs.next() ) {
                System.out.println("compiling" + ((cmdopt.isDryrun() ? "[dry] " : "") + (cmdopt.isLogonly() ? "[log] " : "")) + "\t" + rs.getString("name") + ".." );
                if ( !cmdopt.isDryrun() ) {
                    // compile sql-object unless "-y" is passed
                    if ( !cmdopt.isLogonly() ) {
                        drv.getCompiler().compile(cmdopt, drv, conn, cmdopt.getPath() + File.separator + rs.getString("name").replace('\\', File.separator.toCharArray()[0]));
                        if ( drv.getTranCount(conn) > 0 ) System.out.println("Warning: transaction remained uncommited after " + cmdopt.getPath() + File.separator + rs.getString("name").replace('\\', File.separator.toCharArray()[0]));
                    }
                    bRowExists = false;
                    if ( !cmdopt.isNolog() ) {
                        // update hst_bsql
                        if ( !cmdopt.isSqllst() ) {
                            query = "select count(*) as \"rows\" from hst_bsql where hbs_file=lower(\"" + projectName + "\\" + rs.getString("name") + "\")";
                            rs1 = stmtRows.executeQuery(query);
                            drv.checkForWarning(stmt);
                            logger.info("done.");
                            if ( rs1.next() ) bRowExists = rs1.getInt("rows") > 0;
                            rs1.close();
                        }

                        String userName = System.getProperty("user.name");
                        userName = (userName == null ? "anonymous" : (userName.length() > 20 ? userName.substring(0, 19) : userName));
                        if ( cmdopt.isSqllst() || !bRowExists ) {
                            query = "insert into hst_bsql(hbs_prj, hbs_file, hbs_who, hbs_date) values ( lower(\"" +
                                    (cmdopt.isSqllst() ? projectName:"rev:"+ rs.getString("revision")) +
                                    "\"), lower(\"" + (cmdopt.isSqllst() ? "": (projectName + "\\")) + rs.getString("name") + "\"), \"" + userName +
                                    "\", " + drv.getDateStmt()+")";
                        }
                        else {
                            query = "update hst_bsql set hbs_prj=\"rev:"+ rs.getString("revision") +
                                    "\", hbs_who=\"" + userName + "\", hbs_date=" + drv.getDateStmt() +
                                    " where hbs_file=lower(\"" + (cmdopt.isSqllst() ? "": (projectName + "\\")) + rs.getString("name") + "\")";
                        }

                        logger.info("Executing query [" + query + "]..");
                        stmtRows.executeUpdate(query);
                        drv.checkForWarning(stmt);
                        logger.info("done.");
                    }
                }
            }

            stmtRows.close();
            stmt.close();
        } catch (SQLException ex) {
            throw new JbsqlException("SQL_01", null, ex);
        }
        finally {
            try {
                if (conn != null) conn.close();
            } catch (Exception ex) {
                System.out.println(ex.toString());
            }
        }

        logger.fine("finish");
    }

    /**
     * Create connection.
     * @param drv Driver object instance
     * @return Connection
     * @throws SQLException
     */
    private Connection createConnection(DbDriver drv) throws SQLException {
        logger.info("loader URL: " + drv.getConnectionString(cmdopt.getServerName(), cmdopt.getDatabase()));
        Properties props = new Properties();
        props.put("user", cmdopt.getUserName());
        props.put("password", cmdopt.getPaswd());
        if ( cmdopt.getClientCharset() != null) props.put("CHARSET", cmdopt.getClientCharset());

        return DriverManager.getConnection(drv.getConnectionString(cmdopt.getServerName(), cmdopt.getDatabase()), props);
    }
}

